package com.example.exceldemo.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import java.io.*;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;


@Slf4j
public class ExcelUtils {
	
    /**
     * 替换Excel模板文件内容
     * @param item 文档数据
     * @param sourceFilePath Excel模板文件路径
     * @param targetFilePath Excel生成文件路径
     */
    public static boolean replaceModel(Map<String, String> item, String sourceFilePath) {
        boolean bool = true;
        InputStream fs  = null;
        try {
        	Resource resource = new ClassPathResource(sourceFilePath);
        	log.info("URI:"+resource.getURI());
        	File f = resource.getFile();
        	FileInputStream fis  = new FileInputStream(f);
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            XSSFSheet sheet = wb.getSheetAt(0);
            Iterator<Row> rows = sheet.rowIterator();
            while(rows.hasNext()){
                XSSFRow row = (XSSFRow) rows.next();
                if(row!=null) {
                    int num = row.getLastCellNum();
                    for(int i=0;i<num;i++) {
                        XSSFCell cell=  row.getCell(i);
                        if(cell!=null) {
                            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                        }
                        if(cell==null || cell.getStringCellValue()==null) {
                            continue;
                        }
                        String value= cell.getStringCellValue();
                        if(!"".equals(value)) {
                            Set<String> keySet = item.keySet();
                            Iterator<String> it = keySet.iterator();
                            while (it.hasNext()) {
                                String text = it.next();
                                if(value.equalsIgnoreCase(text)) {
                                    cell.setCellValue((String)item.get(text));
                                    break;
                                }
                            }
                        } else {
                            cell.setCellValue("");
                        }
                    }
                }
            }
            // 输出文件
            FileOutputStream fileOut = new FileOutputStream(f);
            wb.write(fileOut);
            fileOut.close();

        } catch (Exception e) {
            bool = false;
            e.printStackTrace();
        }finally {
        	if(fs != null) {
        		try {
					fs.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
        	}
        }
        return bool;
    }

    // 测试
    public static void main(String[] args) {

        Map<String, String> item = new HashMap<String, String>();
        item.put("L-00001","23");
        item.put("L-00002","汉字");
        item.put("L-00003","");

        String path =  "excelTemplet/22.xlsx";
        replaceModel(item, path);

    }

}
